set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 400;
set hive.exec.max.dynamic.partitions.pernode = 200;
insert
    overwrite
    table jms_dm.dm_cusc_customer_abnormal_problem_summary_dt
    partition
    (dt)
select customer_id                                                                                 as customer_id           --客户id
     , customer_code                                                                               as customer_code         --客户编码
     , customer_name                                                                               as customer_name         --客户名称
     , customer_network_code                                                                       as customer_network_code --客户所属网点code
     , customer_network_name                                                                       as customer_network_name --客户所属网点name
     , customer_fran_code                                                                          as customer_fran_code    --客户所属加盟商code
     , customer_fran_name                                                                          as customer_fran_name    --客户所属加盟商name
     , customer_agent_code                                                                         as customer_agent_code   --客户所属代理区code
     , customer_agent_name                                                                         as customer_agent_name   --客户所属代理区name
     , order_source_code                                                                           as order_source_code     --订单来源code
     , order_source_name                                                                           as order_source_name     --订单来源名称
     , sum(no_taketimely_flag)                                                                     as no_taketimely_num     --未及时揽收量
     , sum(no_collect_flag)                                                                        as no_collect_num        --不准点交件票量
     , sum(false_taking_flag)                                                                      as false_taking_num      --虚假揽收量
     , sum(no_outtimely_flag)                                                                      as no_outtimely_num      --出港不及时量
     , sum(no_signtimely_flag)                                                                     as no_signtimely_num     --未及时签收量
     , sum(complaint_flag)                                                                         as complaint_num         --客诉量
     , sum(sign_difftime_flag)                                                                     as sign_difftime_num     --超24小时揽签量
     , sum(problem_flag)                                                                           as problem_num           --问题件量
     , sum(if(problem_flag = 1 and array_contains(proble_type_subject_code, '41'), 1,
              0))                                                                                  as lose_problem_num      --问题件遗失量
     , sum(if(problem_flag = 1 and array_contains(proble_type_subject_code, '27'), 1,
              0))                                                                                  as worn_problem_num      --问题件破损量
     , sum(if(problem_flag = 1 and array_contains(proble_type_subject_code, '35'), 1,
              0))                                                                                  as pollute_problem_num   --问题件被污染量
     , sum(if(problem_flag = 1 and !array_contains(proble_type_subject_code, '41') and
              !array_contains(proble_type_subject_code, '27') and !array_contains(proble_type_subject_code, '35'), 1,
              0))                                                                                  as other_problem_num     --问题件其他量
     , sum(no_taketimely_flag) + sum(no_collect_flag) + sum(false_taking_flag) + sum(no_outtimely_flag) +
       sum(no_signtimely_flag) + sum(complaint_flag) + sum(sign_difftime_flag) + sum(problem_flag) as abn_total             --异常总量
     , dt                                                                                          as date_time             --日期
     , sum(nvl(diff_take_input_hour, 0))                                                           as take_input_hour       --揽收总时长
     , sum(if(diff_take_input_hour is not null, 1, 0))                                             as take_input_hour_num   --揽收总时长单量
     , sum(nvl(deliver_aging_sign_difftime, 0))                                                    as deliver_sign_hour     --派签总时长
     , dt
from jms_dm.dm_cusc_customer_abnormal_waybill_detail_dt
where dt <= '{{ execution_date | cst_ds }}'
  and dt >= date_add('{{ execution_date | cst_ds }}', -6)
group by customer_id
       , customer_code
       , customer_name
       , customer_network_code
       , customer_network_name
       , customer_fran_code
       , customer_fran_name
       , customer_agent_code
       , customer_agent_name
       , order_source_code
       , order_source_name
       , dt
    distribute by dt
;
